/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2
PILOT: SCRAP
DATE: 

PROJECT DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		


DETAILS (This Syntax): 
Create files of order dates and screen dates

Input Files:
scrap.CRC_patient_covariates

Output Files: 
scrap.CRC_order_dates
scrap.CRC_screen_dates



/*Copy and paste into your program*/
/*file your patient ids are in*/
%let patients=scrap.CRC_patient_covariates;
/*name of patient id variable to link to Clarity [patient identifier]*/
%let id=raw_patid;
/*name of file you want your output order dates stored in*/
%let out_orders=scrap.CRC_order_dates;
/*name of file you want your output screen dates stored in*/
%let out_screen=scrap.CRC_screen_dates;
/*study period start-10 years;*/
%let start_date='01JUL2006'd;
/*study period end*/
%let end_date='29FEB2020'd;

*****************************************************/;
options compress=yes reuse=yes;
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;

proc sql; create table RD.jpo_crc_screens_lu as select * from scrap.CRC_SCREENS_LU; quit;
proc sql; create table reschdev.jpo_crc_patients as select distinct raw_patid from &patients; quit;

/*pull from surgical history*/
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table CRC_shx as 
select * from connection to mycon (
     SELECT den.*	
  	,shx.[contact date] 
	,shx.[surgical history start date]
     ,shx.[SURGICAL History DATE] 
     ,cpc.screen
    FROM research_dev.dbo.jpo_crc_patients  AS den
     INNER JOIN clarity.dbo.[SURGICAL]        AS shx
      ON den.&ID = shx.[patient identifier]
     INNER JOIN research_dev.dbo.jpo_crc_screens_lu AS cpc
      ON shx.[procedure identifier] = cpc.grouper_records_numeric_id
	where cpc.compiled_context='EAP')
	;      
quit;

data CRC_SHX2 ;
	set CRC_SHX;
	length source $20;
	Source='Surgical Hx';
	month=0;
	day=0;
	format screen_date date9.;
	if [surgical history start date]^=. then
		do;
			screen_date=datepart([surgical history start date]);
			if screen_date>=&start_date and screen_date<=&end_date then 
				output CRC_SHX2;
		end;
	if [surgical history start date]=. then
		do;
			if [surgical history date]^=' ' and [surgical history date]^='NO REPORT' then
				do;
					if index([surgical history date],'/')^=0 then
						do;
							month=input (scan([surgical history date],1,'/'),best4.);
							day=input (scan([surgical history date],2,'/'),best4.);
							year=input (scan([surgical history date],3,'/'),best4.);
						end;
					if index([surgical history date],'-')^=0 then
						do;
							month=input (scan([surgical history date],1,'-'),best4.);
							day=input (scan([surgical history date],2,'-'),best4.);
							year=input (scan([surgical history date],3,'-'),best4.);
						end;
					if month>0 and month<13 and
						day>0 and day<32 and
						year>2005 and year<2022 then
							do;
								screen_date=mdy(month, day, year);
							end;
			if screen_date>=&start_date and screen_date<=&end_date then 
				output CRC_SHX2;
				end;
		end;
run;

*screening orders and results from order_proc;
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table crc_orders as 
select * from connection to mycon (

SELECT distinct
	den.&id
    ,op.[ORDERING DATE]
	,op.[result time]
	,op.[procedure identifier]
	,order_status_c
	,ppc.screen
	,op.[pending order yes/no]
	,op.[order procedure identifer]
FROM
    research_dev.dbo.jpo_crc_patients  AS den
		inner join Clarity.dbo.[ORDER PROCEDURE] as op
			on den.&id = op.[patient identifier] 
        inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
      ON op.[PROCEDURE IDENTIFIER] = ppc.grouper_records_numeric_id);
quit;

data CRC_orders ;
set CRC_orders;
length source $20;
Source='Order proc';
format order_date date9.;
order_date=datepart([ordering date]);
where order_status_c^=4;
if order_date<&start_date then delete;
if order_date>&end_date then delete;
run;

data CRC_screen_procs;
set crc_orders;
format screen_date date9.;
screen_date=datepart([result time]);
where [result time]^=.;
if screen_date<&start_date then delete;
if screen_date<&end_date then delete;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_components as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.[result_status], 
	zo.[order_status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	r.[component lnc identifier],
	cc.[default lnc identifier],
	ppc.screen
from ClarityETL.CLARITY.dbo.[ORDER RESULTS] r
inner join ClarityETL.CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[patient identifier]
left join ClarityETL.CLARITY.dbo.[LAB_STATUS] zr on zr.[lab status]=r.[lab status]
left join ClarityETL.CLARITY.dbo.[ORDER_STATUS] zo on zo.[order status]=o.[order status] 
left join ClarityETL.CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
left join clarity.dbo.[component] cc on cc.[component identifier]=r.[component identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.component_id=r.[component identifier]
where ppc.component_id is not null);

data clarity_LAB_components;
set  clarity_LAB_components; 
length source $20;
format order_date screen_date date9.;
Source='Lab Component';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<&start_date then delete;
if screen_date>&end_date then delete;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_cc as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.[result status], 
	zo.[order status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	cc.[default lnc identifier],
	r.[component lnc identifier],
	ppc.screen
from ClarityETL.CLARITY.dbo.[ORDER RESULTS] r
inner join ClarityETL.CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[PATIENT IDENTIFIER]
left join ClarityETL.CLARITY.dbo.[LAB_STATUS] zr on zr.[lab status]=r.[lab_status]
left join ClarityETL.CLARITY.dbo.[ORDER_STATUS] zo on zo.[order_status]=o.[order_status]
left join ClarityETL.CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure_identifier]
left join clarity.dbo.[component] cc on cc.[component identifier]=r.[component identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.LNC_id=cc.[default lnc identifier] 
where ppc.lnc_id is not null);

data clarity_LAB_LNC_cc;
set  clarity_LAB_LNC_cc; 
length source $20;
format order_date screen_date date9.;
Source='Lab CC LNC';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<&start_date then delete;
if screen_date>&end_date then delete;
run;

proc sql;
create table lab_LN_cc as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	clarity_LAB_components);
quit;

data lab_results;
set clarity_LAB_components lab_LN_cc;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_or as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.[result_status], 
	zo.[order_status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	r.[component lnc identifier],
	ppc.screen
from ClarityETL.CLARITY.dbo.[ORDER RESULTS] r
inner join ClarityETL.CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[PATIENT IDENTIFIER]
left join ClarityETL.CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join ClarityETL.CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join ClarityETL.CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.LNC_id=r.[component lnc identifier] 
where ppc.lnc_id is not null);

data clarity_LAB_LNC_or;
set  clarity_LAB_LNC_or; 
length source $20;
format order_date screen_date date9.;
Source='Lab OR LNC';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<&start_date then delete;
if screen_date>&end_date then delete;
run;

proc sql;
create table lab_LN_OR as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	lab_results);
quit;

/*drop records already included in crc_screen procs*/
proc sql;
create table new_results as
select * from lab_results
where [order procedure identifier] ^in
(select distinct [order procedure identifier] from crc_screen_procs);

/*Screens from HEALTH MAINTENANCE HISTORY*/
proc sql;
connect to odbc as mycon
(required = "dsn=OCHIN_HCN_FENWAY;Trusted_Connection=yes");
create table HMT_hx as select *
from connection to mycon (
SELECT
	DN.&ID
	,HMH.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
	,HMH.[HEALTH MAINTENANCE HISTORY DATE]
FROM
	Clarity.dbo.[HEALTH MAINTENANCE HISTORY] as HMH
		inner join research_dev.dbo.jpo_crc_patients as DN
			on DN.&ID = HMH.[PATIENT_IDENTIFIER]
WHERE
	HMH.[HEALTH MAINTENANCE TOPIC IDENTIFIER] IN (30,31,32,75)
	and HMH.[HM TYPE] = 1 /*--HMO_STATUS_ID of 'Done'*/
	and HMH.[HEALTH MAINTENANCE HISTORY DATE] is not null);

data HMT_HX;
set HMT_HX;
length source $20 screen $25;
format screen_date date9.;
screen_date=datepart([HEALTH MAINTENANCE HISTORY DATE]);
Source='HMT HX';
if screen_date<&start_date then delete;
if screen_date>&end_date then delete;
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=30 then screen='FOBT';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=75 then screen='FIT';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=31 then screen='Colonoscopy';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=32 then screen='Flexible Sigmoidoscopy';
run;

/*combine orders*/
data orders;
set crc_orders new_results;
run;

*deduplicate;
proc sql;
create table orders2  as
select &id,
screen,
order_date,
[order procedure identifier],
count([order procedure identifier]) as records,
count(distinct source) as sources
from orders
group by &id,
screen,
order_date,
[order procedure identifier];

data &out_orders;
set orders2;
run;

/* combine screens */
data screens;
set crc_screen_procs new_results crc_shx2 HMT_HX;
run;

/*deduplicate*/
proc sql;
create table &out_screen as
select &id,
screen,
screen_date,
sum(source='HMT HX') as HMT_HX_records,
sum(source in('Lab CC LNC','Lab Component')) as Lab_re_records,
sum(source='Order proc') as ord_proc_records,
sum(source='Surgical Hx') as Surg_hx_records
from screens
group by &id,
screen,
screen_date;

/*add next due*/
data &out_screen;
set &out_screen;
format next_due date9.;
if screen='Colonoscopy' then next_due=intnx('month',screen_date,120,'B');
if screen in ('CT Colonography', 'Flexible Sigmoidoscopy') then
	next_due=intnx('month',screen_date,60,'B');
if screen='FIT' then next_due=intnx('month',screen_date,36,'B');
if screen='FOBT' then next_due=intnx('month',screen_date,12,'B');
run;

data scrap.CRC_screen_dates;
set scrap.CRC_screen_dates;
format next_due2 date9.;
if screen='Colonoscopy' then next_due2=intnx('month',screen_date,120,'s');
if screen in ('CT Colonography', 'Flexible Sigmoidoscopy') then
	next_due2=intnx('month',screen_date,60,'s');
if screen='FIT' then next_due2=intnx('month',screen_date,36,'s');
if screen='FOBT' then next_due2=intnx('month',screen_date,12,'s');
run;
